<?php

namespace Modules\Site\Http\Controllers;

use App\Imports\ProductsImport;
use App\Services\RabbitmqService;
use Maatwebsite\Excel\Facades\Excel;
use Modules\Site\Http\Controllers\CrudController;
use Illuminate\Http\Request;
use Illuminate\Routing\Controller;
use Illuminate\Support\Facades\DB;
use Modules\Site\Http\Models\Products;
use Modules\Site\Http\Models\ProductsDescription;
use Modules\Site\Http\Models\ProductsCategory;
use Modules\Admin\Http\Models\DictionaryValue;
use Modules\Admin\Http\Models\ListStyle;
use Modules\Site\Http\Models\ProductsUploadLog;
use Modules\Site\Http\Models\Region;
// use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;
use App\xlsx\ReaderEntityFactory;
use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
use Box\Spout\Writer\Common\Creator\Style\StyleBuilder;
use Modules\Admin\Http\Models\Publisher;
use Modules\Admin\Http\Models\Site;
use Modules\Site\Http\Models\ProductsExcelField;

class ProductsUploadLogController extends CrudController
{


    // /**
    //  * 查询列表页
    //  * @param $request 请求信息
    //  * @param int $page 页码
    //  * @param int $pageSize 页数
    //  * @param Array $where 查询条件数组 默认空数组
    //  */
    // protected function list(Request $request)
    // {
    //     try {
    //         $this->ValidateInstance($request);
    //         $ModelInstance = $this->ModelInstance();
    //         $model = $ModelInstance->query();
    //         $model = $ModelInstance->HandleWhere($model, $request);
    //         // 总数量
    //         $total = $model->count();
    //         // 查询偏移量
    //         if (!empty($request->pageNum) && !empty($request->pageSize)) {
    //             $model->offset(($request->pageNum - 1) * $request->pageSize);
    //         }
    //         // 查询条数
    //         if (!empty($request->pageSize)) {
    //             $model->limit($request->pageSize);
    //         }
    //         $model = $model->select($ModelInstance->ListSelect);
    //         // 数据排序
    //         $sort = (strtoupper($request->sort) == 'DESC') ? 'DESC' : 'ASC';
    //         if (!empty($request->order)) {
    //             $model = $model->orderBy($request->order, $sort);
    //         } else {
    //             $model = $model->orderBy('sort', $sort)->orderBy('created_at', 'DESC');
    //         }

    //         $record = $model->get();

    //         foreach ($record as $key => $item) {
    //             if ($item['details']) {
    //                 $item['details'] = str_replace("\r\n", "<br />", $item['details']);
    //             } else {
    //                 $item['details'] = '';
    //             }
    //         }

    //         $data = [
    //             'total' => $total,
    //             'list' => $record
    //         ];
    //         ReturnJson(TRUE, trans('lang.request_success'), $data);
    //     } catch (\Exception $e) {
    //         ReturnJson(FALSE, $e->getMessage());
    //     }
    // }

    /**
     * 获取该站点的出版商,下拉选择框数据
     * @param $request 请求信息
     */
    public function getPublisher(Request $request)
    {

        $site = $request->header('Site');
        $publisherIds = Site::where('name', $site)->value('publisher_id');
        $data = [];
        if ($publisherIds) {
            $publisherIdArray = explode(',', $publisherIds);
            $data = (new Publisher())->GetListLabel(['id as value', 'name as label'], false, '', ['status' => 1, 'id' => $publisherIdArray]);
        }

        ReturnJson(TRUE, trans('lang.request_success'), $data);
    }



    /**
     * 批量上传报告
     * @param $request 请求信息
     */
    public function uploadProducts(Request $request)
    {
        $basePath = public_path() . '/site';
        $basePath .= '/' . $request->header('Site') . '/';

        // $basePath = public_path();
        //检验目录是否存在
        if (!is_dir($basePath)) {
            @mkdir($basePath, 0777, true);
        }
        // 出版商id
        $publisher_id = $request->pulisher_id;
        // 操作者id
        $user = \Illuminate\Support\Facades\Auth::user();
        if (isset($user->id)) {
            $userID = $user->id;
        } else {
            $userID = 0;
        }

        //上传文件路径
        $pathsStr = $request->path;
        if (empty($publisher_id) || empty($pathsStr)) {
            ReturnJson(TRUE, trans('lang.param_empty'));
        }


        $paths = explode(',', $pathsStr);
        // return $paths;
        // $productsImport = new ProductsImport();
        // $productsImport->site = $request->header('Site') ?? '';
        // $productsImport->log_id = $logModel->id;
        // foreach ($paths as $key => $value) {
        //     $path = $basePath . $value;
        //     // return $path;
        //     Excel::import($productsImport, $path);
        // }

        //获取表头与字段关系
        $fieldData = ProductsExcelField::where(['status' => 1])->select(['field'])->orderBy('sort', 'asc')->get()->toArray();

        foreach ($fieldData as $key => $value) {
            if (!empty($value['field'])) {
                $fieldData[$key]['sort'] = $key;
            } else {
                unset($fieldData[$key]);
            }
        }
        $fieldData = array_column($fieldData, 'field', 'sort');
        // $fieldSort = array_keys($fieldData);

        // return $fieldSort;
        // 获取总行数
        // $totalRows = 0;
        $logIds = [];
        foreach ($paths as $key => $value) {
            $path = $basePath . $value;

            //上传记录初始化,每个文件单独一条记录
            $logModel = ProductsUploadLog::create([
                'file' => $value
            ]);
            $logIds[] = $logModel->id;
            $data = [
                'class' => 'Modules\Site\Http\Controllers\ProductsUploadLogController',
                'method' => 'handleExcelFile',
                'site' => $request->header('Site') ?? '',   //站点名称
                'log_id' => $logModel->id,  //写入日志的id
                'data' => $path,    //传递文件路径
                'fieldData' => $fieldData,  //字段与excel表头的对应关系
                'publisher_id' => $publisher_id,  //出版商id
                'user_id' => $userID,  //用户id

            ];
            $data = json_encode($data);
            $RabbitMQ = new RabbitmqService();
            $RabbitMQ->setQueueName('products-file-queue'); // 设置队列名称
            $RabbitMQ->setExchangeName('Products'); // 设置交换机名称
            $RabbitMQ->setQueueMode('direct'); // 设置队列模式
            $RabbitMQ->setRoutingKey('productsKey1');
            $RabbitMQ->push($data); // 推送数据
            // 打开 Excel 文件
            // $reader = ReaderEntityFactory::createXLSXReader();
            // $reader->setShouldPreserveEmptyRows(true);
            // $reader->open($path);

            // foreach ($reader->getSheetIterator() as $sheet) {
            //     // $totalRows += $sheet->getTotalRows();
            // }

            // // 关闭文件
            // $reader->close();
        }
        $logIds = implode(',', $logIds);
        // return $totalRows;
        ReturnJson(TRUE, trans('lang.request_success'), $logIds);
    }

    /**
     * 批量上传报告/队列处理文件
     * @param $params 
     */
    public function handleExcelFile($params = null)
    {
        ini_set('memory_limit', '4096M');
        try {
            //读取文件
            $path = $params['data'];
            $fieldData = $params['fieldData'];
            $fieldSort = array_keys($fieldData);
            $reader = ReaderEntityFactory::createXLSXReader($path);
            $reader->setShouldPreserveEmptyRows(true);
            $reader->setShouldFormatDates(true);
            $reader->open($path);   //读取文件
            $excelData = [];

            foreach ($reader->getSheetIterator() as $sheetKey => $sheet) {
                // if ($sheetKey != 1) {
                //     continue;
                // }
                foreach ($sheet->getRowIterator() as $rowKey => $sheetRow) {
                    if ($rowKey == 1) {
                        //表头跳过
                        continue;
                    }
                    $tempRow =  $sheetRow->toArray(); //单行数据
                    $row = [];
                    foreach ($tempRow as $tempKey => $tempValue) {
                        if (in_array($tempKey, $fieldSort)) {
                            $row[$fieldData[$tempKey]] = $tempValue;
                        }
                    }
                    if (count($row) > 0) {
                        $excelData[] = $row;
                    }
                }
            }
            // 设置当前租户
            tenancy()->initialize($params['site']);
            //记录任务状态、总数量
            $logModel = ProductsUploadLog::where(['id' => $params['log_id']])->first();
            $logData = [
                'count' => count($excelData),
                'state' => ProductsUploadLog::UPLOAD_READY,
            ];
            $logModel->update($logData);
            //加入队列
            if ($excelData && count($excelData) > 0) {
                $groupData = array_chunk($excelData, 100);
                foreach ($groupData as $item) {
                    $data = [
                        'class' => 'Modules\Site\Http\Controllers\ProductsUploadLogController',
                        'method' => 'handleProducts',
                        'site' => $params['site'],
                        'log_id' => $params['log_id'],
                        'publisher_id' => $params['publisher_id'],
                        'data' => $item,
                        'user_id' => $params['user_id'],
                    ];
                    $data = json_encode($data);
                    $RabbitMQ = new RabbitmqService();
                    $RabbitMQ->setQueueName('products-queue'); // 设置队列名称
                    $RabbitMQ->setExchangeName('Products'); // 设置交换机名称
                    $RabbitMQ->setQueueMode('direct'); // 设置队列模式
                    $RabbitMQ->setRoutingKey('productsKey2');
                    $RabbitMQ->push($data); // 推送数据
                }
            }

            //code...
        } catch (\Throwable $th) {
            // file_put_contents('ddddddddddd.txt', $th->getLine() . $th->getMessage() . $th->getTraceAsString(), FILE_APPEND);
        }
    }


    /**
     * 批量上传报告/队列消费
     * @param $params['data'] 报告数据
     * @param $params['site'] 站点
     */
    public function handleProducts($params = null)
    {
        // exit;
        if (empty($params['site'])) {
            throw new \Exception("site is empty", 1);
        }

        // 设置当前租户
        tenancy()->initialize($params['site']);
        // tenancy()->initialize('QY_EN');
        $publisher_id = $params['publisher_id'];
        $user_id = $params['user_id'];

        // $count = 0;
        $insertCount = 0;
        $updateCount = 0;
        $errorCount = 0;
        $details = '';

        //移除事件监听
        $dispatcher = Products::getEventDispatcher();
        Products::unsetEventDispatcher();

        foreach ($params['data'] as $row) {
            // $count++;
            try {
                // 表头
                $item = [];
                //出版商
                $item['publisher_id'] = $publisher_id;
                //操作用户
                $item['created_by'] = $user_id;
                $item['updated_by'] = $user_id;
                // 报告名称
                isset($row['name']) && $item['name'] = $row['name'];
                // 报告名称(英)
                isset($row['english_name']) && $item['english_name'] = $row['english_name'];
                // 页数
                isset($row['pages']) && $item['pages'] = $row['pages'];
                // 图表数
                isset($row['tables']) && $item['tables'] = $row['tables'];
                // 基础价
                isset($row['price']) && $item['price'] = $row['price'];

                try {
                    // 出版时间
                    isset($row['published_date']) && $item['published_date'] = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($row['published_date']); //转为 时间戳
                } catch (\Throwable $th) {
                    //throw $th;
                }
                if (!isset($item['published_date']) || $item['published_date'] < 0) {
                    $item['published_date'] = strtotime($row['published_date']);
                }
                // file_put_contents('C:\\Users\\Administrator\\Desktop\\123.txt',json_encode($item['published_date']),FILE_APPEND);

                // 报告分类
                isset($row['category_id']) && $item['category_id'] = ProductsCategory::where('name', trim($row['category_id']))->value('id') ?? 0;
                //报告所属区域
                isset($row['country_id']) && $item['country_id'] = Region::where('name', trim($row['country_id']))->value('id') ?? 0;
                //作者
                isset($row['author']) && $item['author'] = $row['author'];
                //关键词
                isset($row['keywords']) && $item['keywords'] = $row['keywords'];
                //自定义链接
                isset($row['url']) && $item['url'] = $row['url'];
                // 如果链接为空，则用关键词做链接
                if (!empty($row['keywords']) && empty($row['url'])) {
                    $item['url'] = $row['keywords'];
                    $item['url'] = strtolower(preg_replace('/%[0-9A-Fa-f]{2}/', '-', urlencode(str_replace(' ', '-', trim($item['url'])))));
                    $item['url'] = strtolower(preg_replace('/[^A-Za-z0-9-]/', '-', urlencode(str_replace(' ', '-', trim($item['url'])))));
                    $item['url'] = trim($item['url'], '-'); //左右可能有多余的横杠
                }

                //详情数据
                $itemDescription = [];
                isset($row['description']) && $itemDescription['description'] = str_replace('_x000D_', '', $row['description']);
                isset($row['table_of_content']) && $itemDescription['table_of_content'] = str_replace('_x000D_', '', $row['table_of_content']);
                isset($row['tables_and_figures']) && $itemDescription['tables_and_figures'] = str_replace('_x000D_', '', $row['tables_and_figures']);
                isset($row['description_en']) && $itemDescription['description_en'] = str_replace('_x000D_', '', $row['description_en']);
                isset($row['table_of_content_en']) && $itemDescription['table_of_content_en'] = str_replace('_x000D_', '', $row['table_of_content_en']);
                isset($row['tables_and_figures_en']) && $itemDescription['tables_and_figures_en'] = str_replace('_x000D_', '', $row['tables_and_figures_en']);
                isset($row['companies_mentioned']) && $itemDescription['companies_mentioned'] = str_replace('_x000D_', '', $row['companies_mentioned']);



                /** 
                 * 不合格的数据过滤
                 */

                // 忽略报告名为空的数据
                if (empty($item['name'])) {
                    $details .= trans('lang.name_empty') . "\r\n";
                    $errorCount++;
                    continue;
                }
                // 忽略基础价为空的数据
                if (empty($item['price'])) {
                    $details .= '【' . ($row['name'] ?? '') . '】' . trans('lang.price_empty') . "\r\n";
                    $errorCount++;
                    continue;
                }
                // 忽略出版时间为空或转化失败的数据
                if (empty($item['published_date']) || $item['published_date'] < 0) {
                    $details .= '【' . ($row['name'] ?? '') . '】' . trans('lang.published_date_empty') . "\r\n";
                    $errorCount++;
                    continue;
                }
                // 忽略分类为空的数据
                if (empty($item['category_id'])) {
                    $details .= '【' . ($row['name'] ?? '') . '】' . $row['category_id'] . '-' . trans('lang.category_empty') . "\r\n";
                    $errorCount++;
                    continue;
                }

                // 忽略关键词为空的数据
                if (empty($item['keywords'])) {
                    $details .= '【' . ($row['name'] ?? '') . '】'  . trans('lang.keywords_empty') . "\r\n";
                    $errorCount++;
                    continue;
                }

                // 忽略url为空的数据
                if (empty($item['url'])) {
                    $details .= '【' . ($row['name'] ?? '') . '】' . trans('lang.url_empty') . "\r\n";
                    $errorCount++;
                    continue;
                }
                // 查询单个报告数据/去重
                $product = Products::where('name', trim($item['name']))->orWhere('name', isset($row['english_name']) ? trim($row['name']) : '')->first();

                // 过滤不符合作者覆盖策略的数据
                if ($product) {
                    if (($product->author == '已售报告' && $item['author'] != '已售报告')
                            || ($product->author == '完成报告' && ($item['author'] != '已售报告' && $item['author'] != '完成报告'))
                    ) {
                        $details .= '【' . ($row['name'] ?? '') . '】' . ($item['author']) . '-' . trans('lang.author_level') . ($product->author) . "\r\n";
                        $errorCount++;
                        continue;
                    }
                }


                //新纪录年份
                $newYear = Products::publishedDateFormatYear($item['published_date']);
                /** 
                 * 数据库操作
                 */
                if ($product) {
                    $itemDescription['product_id'] = $product->id;
                    //旧纪录年份
                    $oldPublishedDate = $product->published_date;
                    $oldYear = Products::publishedDateFormatYear($oldPublishedDate);
                    //更新报告
                    $product->update($item);

                    $newProductDescription = (new ProductsDescription($newYear));
                    //出版时间年份更改
                    if ($oldYear != $newYear) {
                        //删除旧详情
                        if ($oldYear) {
                            $oldProductDescription = (new ProductsDescription($oldYear))->where('product_id', $product->id)->first();
                            if ($oldProductDescription) {
                                $oldProductDescription->delete();
                            }
                        }
                        //然后新增
                        $descriptionRecord = $newProductDescription->saveWithAttributes($itemDescription);
                    } else {
                        //直接更新
                        $newProductDescriptionUpdate = $newProductDescription->where('product_id', $product->id)->first();
                        if ($newProductDescriptionUpdate) {
                            $descriptionRecord = $newProductDescriptionUpdate->updateWithAttributes($itemDescription);
                        } else {
                            $descriptionRecord = $newProductDescription->saveWithAttributes($itemDescription);
                        }
                    }
                    $updateCount++;
                    (new Products)->PushXunSearchMQ(['id' => $product->id],'update',$params['site']);

                } else {
                    //新增报告
                    $product = Products::create($item);
                    //新增报告详情
                    $newProductDescription = (new ProductsDescription($newYear));
                    $itemDescription['product_id'] = $product->id;
                    $descriptionRecord = $newProductDescription->saveWithAttributes($itemDescription);
                    $insertCount++;
                    (new Products)->PushXunSearchMQ(['id' => $product->id],'add',$params['site']);

                }
                //code...
            } catch (\Throwable $th) {
                //throw $th;
                $details .= '【' . ($row['name'] ?? '') . '】' . $th->getMessage() . "\r\n";
                // $details = $th->getLine().$th->getMessage().$th->getTraceAsString() . "\r\n";
                // $details = json_encode($row) . "\r\n";
                $errorCount++;
            }
        }

        //恢复监听
        Products::setEventDispatcher($dispatcher);
        try {
            DB::beginTransaction();
            $logModel = ProductsUploadLog::where(['id' => $params['log_id']])->first();
            $logData = [
                // 'count' => ($logModel->count ?? 0) + $count,
                'insert_count' => ($logModel->insert_count ?? 0) + $insertCount,
                'update_count' => ($logModel->update_count ?? 0) + $updateCount,
                'error_count' => ($logModel->error_count ?? 0) + $errorCount,
                'details' => ($logModel->details ?? '')  . $details,

            ];
            //如果数量吻合，则证明上传完成了
            if ($logModel->count == $logData['insert_count'] + $logData['update_count'] + $logData['error_count']) {
                $logData['state'] = ProductsUploadLog::UPLOAD_COMPLETE;
            } else {
                $logData['state'] = ProductsUploadLog::UPLOAD_RUNNING;
            }
            $logFlag = $logModel->update($logData);

            if ($logFlag) {
                DB::commit();
            } else {
                DB::rollBack();
            }
        } catch (\Exception $e) {
            DB::rollBack();
            throw $e;
        }
    }


    /**
     * 上传进度
     * @param $request 请求信息
     */
    public function uploadProcess(Request $request)
    {
        $logIds = $request->ids;
        if (empty($logIds)) {
            ReturnJson(TRUE, trans('lang.param_empty'));
        }
        $logIdsArray = explode(',', $logIds);
        // return $logIdsArray;
        $logData = ProductsUploadLog::whereIn('id', $logIdsArray)->get()->toArray();
        $data = [
            'result' => true,
            'msg' => '',
        ];
        $text = '';
        $updateTime = 0;
        foreach ($logData as $key => $value) {
            if ($value['state'] != ProductsUploadLog::UPLOAD_COMPLETE) {
                $data['result'] = false;
            }
            $updatedTimestamp = strtotime($value['updated_at']);
            if ($updatedTimestamp > $updateTime) {
                $updateTime = $updatedTimestamp;
            }
            switch ($value['state']) {
                case ProductsUploadLog::UPLOAD_INIT:
                    $text .= '【' . $value['file'] . '】' . trans('lang.upload_init_msg') . "\r\n";
                    break;

                case ProductsUploadLog::UPLOAD_READY:
                    $text .= '【' . $value['file'] . '】' . trans('lang.upload_ready_msg') . "\r\n";
                    break;

                case ProductsUploadLog::UPLOAD_RUNNING:
                    $text .= '【' . $value['file'] . '】' . trans('lang.upload_running_msg') . ($value['insert_count'] + $value['update_count'] + $value['error_count']) . '/' . $value['count'] . "\r\n";
                    break;

                case ProductsUploadLog::UPLOAD_COMPLETE:
                    $text .= '【' . $value['file'] . '】' . trans('lang.upload_complete_msg') . "\r\n";
                    break;

                default:
                    # code...
                    break;
            }
        }
        $data['msg'] = $text;
        //五分钟没反应则提示
        if (time() > $updateTime + 60 * 5) {

            $data = [
                'result' => true,
                'msg' => trans('lang.time_out'),
            ];
        }


        ReturnJson(TRUE, trans('lang.request_success'), $data);
    }


    /**
     * 上传示例文件,根据设置excel字段生成
     * @param $request 请求信息
     */
    public function exampleFile(Request $request)
    {

        // $site = $request->header('Site');
        // if (empty($site)) {
        //     ReturnJson(TRUE, trans('lang.param_empty'));
        // }

        $writer = WriterEntityFactory::createXLSXWriter();
        $writer->openToBrowser('sample.xlsx'); // 将文件输出到浏览器并下载

        //获取表头与字段关系
        $fieldData = ProductsExcelField::where(['status' => 1])->select(['name', 'field'])->orderBy('sort', 'asc')->get()->toArray();
        $title = array_column($fieldData, 'name');
        foreach ($fieldData as $key => $value) {
            $fieldData[$key]['sort'] = $key;
        }
        $fieldData = array_column($fieldData, 'field', 'sort');

        //写入标题
        $style = (new StyleBuilder())->setShouldWrapText(false)->build();
        $row = WriterEntityFactory::createRowFromArray($title, $style);
        $writer->addRow($row);

        //读取几条数据当案例
        $record = Products::orderBy('id', 'desc')->limit(5)->get()->makeHidden((new Products())->getAppends())->toArray();
        if ($record && count($record) > 0) {

            foreach ($record as $key => $item) {
                $year = date('Y', $item['published_date']);
                if (empty($year) || !is_numeric($year) || strlen($year) !== 4) {
                    continue;
                }

                $item['published_date'] = date('Y-m-d', $item['published_date']) ?? '';
                if (isset($item['category_id'])) {
                    $item['category_id'] = ProductsCategory::where('id', $item['category_id'])->value('name') ?? '';
                }

                $descriptionData = (new ProductsDescription($year))->where('product_id', $item['id'])->first();
                $item['description'] = $descriptionData['description'] ?? '';
                $item['table_of_content'] = $descriptionData['table_of_content'] ?? '';
                $item['tables_and_figures'] = $descriptionData['tables_and_figures'] ?? '';
                $item['description_en'] = $descriptionData['description_en'] ?? '';
                $item['table_of_content_en'] = $descriptionData['table_of_content_en'] ?? '';
                $item['tables_and_figures_en'] = $descriptionData['tables_and_figures_en'] ?? '';
                $item['companies_mentioned'] = $descriptionData['companies_mentioned'] ?? '';

                $row = [];
                foreach ($fieldData as $value) {
                    if (empty($value) || !isset($item[$value])) {
                        $row[] = '';
                    } else {
                        $row[] = $item[$value];
                    }
                }

                $rowFromValues = WriterEntityFactory::createRowFromArray($row, $style);
                $writer->addRow($rowFromValues);
            }
        }

        $writer->close();
    }
}
